﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace Test.MySql.JsonTests.Primitives
{
    [TestFixture]
    internal class SimpleTypeTests_string : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }

            //varchar
            [JsonStore(Bucket = "p_varchar")]
            public string p_varchar { get; set; }
            [JsonStore(Bucket = "p_datetime")]
            public DateTime? p_datetime { get; set; }
            [JsonStore(Bucket = "p_datetimeoffset")]
            public DateTimeOffset? p_datetimeoffset { get; set; }
            [JsonStore(Bucket = "p_date")]
            public DateOnly? p_date { get; set; }
        }

        [Table("test2")]
        public class PersonKey
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }

            //varchar
            [JsonStore(Bucket = "ext", Key = "p_varchar")]
            public string p_varchar { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_datetime")]
            public DateTime? p_datetime { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_datetimeoffset")]
            public DateTimeOffset? p_datetimeoffset { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_date")]
            public DateOnly? p_date { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("""
                create table test(
                    id int auto_increment primary key,
                    -- int
                    p_varchar json,
                    p_datetime json,
                    p_datetimeoffset json,
                    p_date json
                )
                """);
            db.ExecuteSql("""
                insert into test(p_varchar,p_datetime,p_datetimeoffset,p_date) values
                ('"str"','"2024-07-08T09:55:01.1234560"','"2024-07-08T09:55:01.1234560+08:00"','"2024-07-08"'),
                (null,null,null,null),
                ('"str3"',null,null,null);
                """);

            DropTableIfExist("test2");
            db.ExecuteSql("""
                create table test2(
                    id int auto_increment primary key,
                    ext json
                )
                """);
            db.ExecuteSql("""
                insert into test2(ext) values
                    (json_object    ("p_varchar",'str',"p_datetime",'2024-07-08T09:55:01.1234560',"p_datetimeoffset",'2024-07-08T09:55:0 1 . 1 234560+08:00',"p_date",'2024-07-08')),
                    (json_object("p_varchar",null,"p_datetime",null,"p_datetimeoffset",null,"p_date",null)),
                    (json_object("p_varchar",'str3',"p_datetime",null,"p_datetimeoffset",null,"p_date",null));
                """);
        }

        #region NoKey
        [Test]
        public void TestSelect()
        {
            var select = db.Select<Person>().Where(i => i.p_datetime == null);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":2,"p_varchar":null,"p_datetime":null,"p_datetimeoffset":null,"p_date":null},{"Id":3,"p_varchar":"str3","p_datetime":null,"p_datetimeoffset":null,"p_date":null}]""");
        }

        [Test]
        public void TestInsert()
        {
            var dt = DateTime.Parse("2024-07-08 09:55:01.123456");
            var sql = db.Insert<Person>().SetEntity(
            [
                new Person()
                {
                    p_varchar="str",
                    p_datetime=dt,
                    p_date=DateOnly.Parse(dt.ToCommonDateString()),
                    p_datetimeoffset=DateTimeOffset.Parse(dt.ToGlobalStamp2String())
                },
                new Person()
                {
                    p_varchar=null,
                    p_datetime=null,
                    p_date=null,
                    p_datetimeoffset=null
                },
                new Person()
                {
                    p_varchar="str3",
                    p_datetime=null,
                    p_date=null,
                    p_datetimeoffset=null
                },
            ]).ToSql();
        }

        [Test]
        public void TestDelete()
        {
            var delete = db.Delete<Person>().Where(i => i.p_varchar == null);
            var r = delete.ExecuteAffrows();
            r.ShouldBe(1);
        }

        [Test]
        public void TestUpdate()
        {
            var update = db.Update<Person>().SetEntity(new Person
            {
                Id = 1,
                p_varchar = "ssss",
                p_datetime = new DateTime(1998, 2, 3)
            });
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var json = db.Select<Person>().ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"p_varchar":"ssss","p_datetime":"1998-02-03T00:00:00.0000000","p_datetimeoffset":null,"p_date":null},{"Id":2,"p_varchar":null,"p_datetime":null,"p_datetimeoffset":null,"p_date":null},{"Id":3,"p_varchar":"str3","p_datetime":null,"p_datetimeoffset":null,"p_date":null}]""");
        }
        #endregion

        #region NoKey
        [Test]
        public void TestSelectKey()
        {
            var select = db.Select<PersonKey>().Where(i => i.p_datetime == null);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":2,"p_varchar":null,"p_datetime":null,"p_datetimeoffset":null,"p_date":null},{"Id":3,"p_varchar":"str3","p_datetime":null,"p_datetimeoffset":null,"p_date":null}]""");
        }

        [Test]
        public void TestInsertKey()
        {
            var dt = DateTime.Parse("2024-07-08 09:55:01.123456");
            var sql = db.Insert<PersonKey>().SetEntity(
            [
                new PersonKey()
                {
                    p_varchar="str",
                    p_datetime=dt,
                    p_date=DateOnly.Parse(dt.ToCommonDateString()),
                    p_datetimeoffset=DateTimeOffset.Parse(dt.ToGlobalStamp2String())
                },
                new PersonKey()
                {
                    p_varchar=null,
                    p_datetime=null,
                    p_date=null,
                    p_datetimeoffset=null
                },
                new PersonKey()
                {
                    p_varchar="str3",
                    p_datetime=null,
                    p_date=null,
                    p_datetimeoffset=null
                },
            ]).ToSql();
        }

        [Test]
        public void TestDeleteKey()
        {
            var delete = db.Delete<PersonKey>().Where(i => i.p_varchar == null);
            var r = delete.ExecuteAffrows();
            r.ShouldBe(1);
        }

        [Test]
        public void TestUpdateKey()
        {
            var update = db.Update<PersonKey>().SetEntity(new PersonKey
            {
                Id = 1,
                p_varchar = "ssss",
                p_datetime = new DateTime(1998, 2, 3)
            });
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var json = db.Select<PersonKey>().ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"p_varchar":"ssss","p_datetime":"1998-02-03T00:00:00.0000000","p_datetimeoffset":null,"p_date":null},{"Id":2,"p_varchar":null,"p_datetime":null,"p_datetimeoffset":null,"p_date":null},{"Id":3,"p_varchar":"str3","p_datetime":null,"p_datetimeoffset":null,"p_date":null}]""");
        }
        #endregion
    }
}
